package com.apes.scm.masterdata.frame.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * @program: apes-server
 * @description
 * @author: czs
 * @create: 2021-07-23 18:32
 **/
@Component
public class StoreDao extends ApesDao {


    /**
     * 站点
     * @param param
     * @return
     */
    public JSONArray findFirstByPhyDepotId(JSONObject param) {
        String sSQL = "SELECT * " +
                " FROM TMS_SITE " +
                " WHERE PHY_DEPOT_ID IN (:phy_depot_id)";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询采购组
     * @param param
     * @return
     */
    public JSONArray queryPurchasingGroupDept(JSONObject param) {
        String sSQL = "SELECT G.* FROM CM_PURCHASING_GROUP G WHERE G.ID IN(SELECT A.PURCHASING_ID FROM CM_PURCHASING_DEPT A WHERE A.DEPT_ID = :deptId)";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * DC配送范围
     * @param param
     * @return
     */
    public JSONArray findAllByCLocationId(JSONObject param) {
        String sSQL = "SELECT A.*," +
                " (SELECT L.NAME FROM mas_location L WHERE L.ID = A.F_LOCATION_ID ) F_LOCATION_NAME, " +
                " (SELECT L.NAME FROM mas_location L WHERE L.ID = A.C_LOCATION_ID ) C_LOCATION_NAME " +
                "FROM MAS_DC_DISTRIBUTION_RANGE A " +
                "WHERE A.C_location_id = :ClocationId";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询支线线路
     * @param param
     * @return
     */
    public JSONArray findByShippingAddress(JSONObject param) {
        String sSQL = "SELECT A.* " +
                " FROM TMS_BRANCH_LINE A " +
                " LEFT JOIN TMS_SITE B ON A.DEPARTURE_STATION = B.ID " +
                " WHERE A.ID IN (SELECT I.BRANCH_LINE_ID FROM TMS_BRANCH_LINE_ITEM I WHERE I.PHY_DEPOT_ID IN (:phy_depot_id)) and A.line_type = 'BranchLine'";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询干线线路
     * @param param
     * @return
     */
    public JSONArray findByMainShippingAddress(JSONObject param) {
        String sSQL = "SELECT A.* " +
                " FROM TMS_BRANCH_LINE A " +
                " LEFT JOIN TMS_SITE B ON A.DEPARTURE_STATION = B.ID " +
                " WHERE A.line_type = 'MainLine'";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询仓库库区
     * @param param
     * @return
     */
    public JSONArray findLocationByCompany(JSONObject param) {
        String sSQL = " select * " +
                " from MAS_LOCATION t " +
                " where t.DEPOT_ID in (SELECT t.id FROM MAS_DEPOT T WHERE T.DEPT_ID = :deptId) " +
                " and t.VALID = 1";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询逻辑仓库
     * @param param
     * @return
     */
    public JSONArray findDepotLocationBydept(JSONObject param) {
        String sSQL = " SELECT * " +
                " FROM mas_depot L" +
                " WHERE L.dept_id= :deptId  and L.VALID = 1";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询物理仓库
     * @param param
     * @return
     */
    public JSONArray findPhyLocationBydept(JSONObject param) {
        String sSQL = " SELECT P.* FROM mas_phy_depot P WHERE P.ID IN (" +
                " SELECT L.phy_depot_id " +
                " FROM mas_location L" +
                " WHERE L.company_id = :companyId and L.VALID = 1 and L.phy_depot_id is not null)";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询赋能合同
     * @param param
     * @return
     */
    public JSONArray findFnContractByCompany(JSONObject param) {
        String sSQL = "SELECT * FROM FN_CONTRACT WHERE PARTNER_ID = :companyId AND TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) and state in ('approve', 'extension', 'activation')";

        return toJSONArray(find(sSQL, param));
    }


}
